<?php
require_once('include/permiss.php');
require_once('include/header.php');
require_once('include/menu.php');
require_once('config.php');
?>
<div class="container">
    <div class="box">
        <div class="box-heading">THỐNG KÊ - BÁO CÁO</div>
        <div class="box-content">
            
            <table class="list">
                <tr bgcolor="#EFEFEF" class='report_header'>
                    <td colspan='3'>MÓN ĐẶT NHIỀU</td>
                </tr>
                <tr bgcolor="#EFEFEF">
                    <th class='remove'>SP</th>
                    <th class='name'>Tên món</th>
                    <th class='name'>Số lượng</th>
                </tr>
                <?php
                $pdo = $conn->prepare('SELECT datab_product.product_id, product_name, SUM(order_quantity) AS quantity FROM datab_order_product JOIN datab_product ON datab_order_product.product_id=datab_product.product_id GROUP BY product_id ORDER BY quantity DESC LIMIT 3');
                $pdo->execute();
                if ($pdo->rowCount()) {
                    $data = $pdo->fetchAll();
                    foreach ($data as $row) {
                        ?>
                        <tr>
                            <td class="remove"><?php echo $row['product_id']; ?></td>
                            <td class="name"><?php echo $row['product_name']; ?></td>
                            <td class="name"><?php echo $row['quantity']; ?></td>
                        </tr>    
                        <?php
                    }
                }
                ?>
            </table>

            <?php
            $pdo = $conn->prepare('SELECT datab_order.order_id, customer_name, sum(order_quantity*product_price) AS sumoforder FROM datab_order_product JOIN datab_product ON datab_order_product.product_id=datab_product.product_id JOIN datab_order ON datab_order_product.order_id=datab_order.order_id GROUP BY order_id ORDER BY sumoforder DESC LIMIT 3');
            $pdo->execute();
            ?>
            <table class="list">
                <tr bgcolor="#EFEFEF" class='report_header'>
                    <td colspan='3'>HÓA ĐƠN ĐẶT SỐ LƯỢNG LỚN</td>
                </tr>
                <tr bgcolor="#EFEFEF">
                    <th class='remove'>HĐ</th>
                    <th class='name'>Tên khách hàng</th>
                    <th class='name'>Tổng tiền hóa đơn</th>
                </tr>
                <?php
                if ($pdo->rowCount()) {
                    $data = $pdo->fetchAll();
                    foreach ($data as $row) {
                        ?>
                        <tr>
                            <td class="remove">#<?php echo $row['order_id']; ?></td>
                            <td class="name"><?php echo $row['customer_name']; ?></td>
                            <td class="name"><?php echo formatMoney($row['sumoforder']); ?></td>
                        </tr>    
                        <?php
                    }
                }
                ?>
            </table>
            
            <table class="list">
                <tr bgcolor="#EFEFEF" class='report_header'>
                    <td colspan='3'>SỐ LƯỢNG MÓN ĂN</td>
                </tr>
                <tr>
                    <?php
                    $pdo = $conn->prepare('SELECT count(product_id) AS soluong FROM datab_product');
                    $pdo->execute();
                    $soluong = $pdo->fetchColumn();
                    ?>
                    <td class='remove'>Số lượng món ăn:</td>
                    <td class='name'><?php echo $soluong; ?></td>
                </tr>
            </table>

            <table class="list">
                <tr bgcolor="#EFEFEF" class='report_header'>
                    <td colspan='3'>HÓA ĐƠN</td>
                </tr>
                <tr>
                    <?php
                    $pdo = $conn->prepare('SELECT count(order_id) AS hoadon FROM datab_order GROUP BY order_status');
                    $pdo->execute();
                    $hoadon = $pdo->fetchColumn();
                    ?>
                    <td class='remove'>Số hóa đơn đã xuất:</td>
                    <td class='name'><?php echo $hoadon; ?></td>
                </tr>
            </table>

            <table class="list">
                <tr bgcolor="#EFEFEF" class='report_header'>
                    <td colspan='3'>TỔNG TIỀN</td>
                </tr>
                <tr>
                    <?php
                    $pdo = $conn->prepare('SELECT SUM(order_quantity*product_price) AS total FROM datab_product JOIN datab_order_product ON datab_product.product_id=datab_order_product.product_id');
                    $pdo->execute();
                    $total = $pdo->fetchColumn();
                    ?>
                    <td class='remove'>Tổng tiền thu được</td>
                    <td class='name'><?php echo formatMoney($total); ?></td>
                </tr>
            </table>

        </div>
    </div>    
</div>
<?php
require_once('include/footer.php');
?>
 
